<?php
class omeanalysts_mdl_ome_stockuprefund extends dbeav_model{

    //var $has_export_cnf = true;

    var $export_name = '发货异常明细表';

    public function table_name($real=false){
        $table_name = 'ome_delivery';
        if($real){
            return kernel::database()->prefix.$table_name;
        }else{
            return $table_name;
        }
    }

    public function count($filter=null){
        $where = $this->_filter($filter); 

        $sql = "SELECT  DISTINCT count(*) as _count from sdb_ome_orders os 
                    left join sdb_ome_order_items ositems on ositems.order_id =os.order_id
                    left JOIN sdb_ome_delivery_order odo on (odo.order_id = os.order_id)
                    left JOIN sdb_ome_delivery od on ( od.delivery_id = odo.delivery_id )
                    left join sdb_wms_delivery e on (e.outer_delivery_bn =od.delivery_bn)
                    left join sdb_wms_delivery_bill db on e.delivery_id = db.delivery_id 
                    INNER join sdb_wms_delivery_items items on e.delivery_id=items.delivery_id AND items.bn=ositems.bn 
                    left join sdb_ome_shop f on os.shop_id = f.shop_id
                    left JOIN sdb_material_basic_material_ext ext on ositems.product_id=ext.bm_id 
                   where  ".$where." and os.is_fail='false'  and (os.pay_status in('4','5','6','7') or os.pause='true') ";
        $row = $this->db->select($sql);
        return intval($row[0]['_count']);


    }

    public function getlist($cols='*', $filter=array(), $offset=0, $limit=-1, $orderType=null){
  

        if(!$filter['time_from'] && !$filter['time_to'])
        {
            return false;
        }else{

            $where = $this->_filter($filter); 
            //已备货退款中的订单、发货单、物流单号
           //订单号、店铺、客户编码、客户名称、订单时间、发货单号、物流单号、物料、单位、数量、是否暂停、是否校验、是否打印、是否备货
 //var_dump($where);
            $sql = "SELECT DISTINCT os.order_bn ,f.`name`,f.AXCustomer,f.customername,os.createtime,e.delivery_bn,db.logi_no,ositems.bn,ext.unit,items.number,
                    case when os.pause=true then '是' ELSE '否' end pause,
                    case when e.is_form=true then '是' ELSE '否' end is_form,
                    case WHEN e.process_status=0 then '是' else '否'  end isprint,
                    case when e.process_status=3 then  '是' else '否'  end ischeck,
                    os.ship_status,os.pay_status
                    from sdb_ome_orders os 
                    left join sdb_ome_order_items ositems on ositems.order_id =os.order_id
                    left JOIN sdb_ome_delivery_order odo on (odo.order_id = os.order_id)
                    left JOIN sdb_ome_delivery od on ( od.delivery_id = odo.delivery_id )
                    left join sdb_wms_delivery e on (e.outer_delivery_bn =od.delivery_bn)
                    left join sdb_wms_delivery_bill db on e.delivery_id = db.delivery_id 
                    INNER JOIN sdb_wms_delivery_items items ON e.delivery_id=items.delivery_id AND items.bn=ositems.bn 
                    left join sdb_ome_shop f on os.shop_id = f.shop_id
                    left JOIN sdb_material_basic_material_ext ext on ositems.product_id=ext.bm_id

                    where  ".$where." and os.is_fail='false' and (os.pay_status in('4','5','6','7') or os.pause='true')  ";
                     
            $rows = $this->db->selectLimit($sql,$limit,$offset);
          // var_dump($sql);
             $pay_status = array (
                      '0' => '未支付',
                      '1' => '已支付',
                      '2' => '处理中',
                      '3' => '部分付款',
                      '4' => '部分退款',
                      '5' => '全额退款',
                      '6' => '退款申请中',
                      '7' => '退款中',
                      '8' => '支付中',
              );
            foreach ($omedelivery as $key => $value) 
            {
              $omedelivery[$key]['pay_status']=$pay_status[$value['pay_status']];
            }
            return $rows;
        }
        
    }
    
    public function searchOptions(){
        $parentOptions = parent::searchOptions();
        $childOptions = array();
        return $Options = array_merge($parentOptions,$childOptions);
    }

    public function _filter($filter,$tableAlias=null,$baseWhere=null)
    {
//店铺、客户、订单号、发货单号、物流单号、物料编码

        $where = array(1);
       if(empty($filter['time_from']))
        {
          $where[]=' 1=2 ';
        }
        if(isset($filter['time_from']) && $filter['time_from']){
            $where[] = ' os.createtime >='.strtotime($filter['time_from']);
        }
        if(isset($filter['time_to']) && $filter['time_to']){
            $where[] = ' os.createtime <'.(strtotime($filter['time_to'])+86399);
        }
        if(isset($filter['AXCustomer']) && $filter['AXCustomer']){
            $where[]= ' f.AXCustomer  =\''.addslashes($filter['AXCustomer']).'\'';

        }  
         if(isset($filter['order_bn']) && $filter['order_bn']){
            $where[]= ' os.order_bn  =\''.addslashes($filter['order_bn']).'\'';

        }  
        if(isset($filter['delivery_bn']) && $filter['delivery_bn']){
            $where[]= ' e.delivery_bn  =\''.addslashes($filter['delivery_bn']).'\'';

        }  
        // if(isset($filter['logi_no']) && $filter['logi_no']){
        //     $where[]= ' db.logi_no  =\''.addslashes($filter['logi_no']).'\'';

        // }  
                   
        if(isset($filter['p_bn']) && $filter['p_bn']){
            $where[] = ' items.bn =\''.addslashes($filter['p_bn']).'\'';
        }
       if($filter['pay_status']=='9'){
            $where[]=" os.pay_status ='0' ";
       }else if(isset($filter['pay_status']) && $filter['pay_status']){
            $where[]= ' os.pay_status  =\''.addslashes($filter['pay_status']).'\'';

        }  

        
        if ($filter['ship_status']=='5'){
          $where[]= "os.ship_status ='0' ";
        }
        else if(isset($filter['ship_status']) && $filter['ship_status']){
            $where[]= ' os.ship_status  =\''.addslashes($filter['ship_status']).'\'';

        }  
        if($filter['is_form']=='1' ){
             $where[] = " e.is_form ='false' ";
        }else if($filter['is_form']=='2'){
             $where[] = " e.is_form ='true' ";
        } 
          if($filter['pause']=='1' ){
             $where[] = " os.pause ='false' ";
        }else if ($filter['pause']=='2') {
         $where[] = " os.pause ='true' ";
        }


        return implode($where,' AND ');
    }

    public function get_schema() 
    {
      //订单号、店铺、客户编码、客户名称、订单时间、发货单号、物流单号、物料、单位、数量、是否暂停、是否校验、是否打印、是否备货
        $schema = array (
            'columns' => array (
               'order_bn' =>
                array(
                    'type' => 'varchar(32)',
                    'required' => true,
                    'label' => '订单号',
                    'is_title' => true,
                    'width' => 125,
                    'searchtype' => 'nequal',
                    'editable' => false,
                    'filtertype' => 'normal',
                    'filterdefault' => true,
                    'in_list' => true,
                    'default_in_list' => false,
                ),
                 'name' =>
                array (
                  'type' => 'varchar(255)',
                  'required' => true,
                  'label' => '店铺',
                  'editable' => false,
                  'searchtype' => 'has',
                  'filtertype' => 'normal',
                  'filterdefault' => true,
                  'in_list' => true,
                  'default_in_list' => true,
                  'is_title' => true,
                  'width' => '120',
                ),
                'AXCustomer' =>
                array (
                  'type' => 'varchar(255)',
                  'label' => '客户编码',
                  'editable' => false,
                  'searchtype' => 'has',
                  'filtertype' => 'normal',
                  'filterdefault' => true,
                  'in_list' => true,
                  'default_in_list' => true,
                  'is_title' => true,
                  'width' => '120',
                ),
                 'customername' =>
                array (
                  'type' => 'varchar(255)',
                  'label' => '客户名称',
                  'editable' => false,
                  'searchtype' => 'has',
                  'filtertype' => 'normal',
                  'filterdefault' => true,
                  'in_list' => true,
                  'default_in_list' => true,
                  'is_title' => true,
                  'width' => '120',
                ),
               'createtime' =>
              array (
                'type' => 'time',
                'label' => '订单时间',
                'comment' => '订单时间',
                'editable' => false,
                'in_list' => true,
              ),
                'delivery_bn' =>
                array (
                  'type' => 'varchar(32)',
                  'required' => true,
                  'label' => '发货单号',
                  'editable' => false,
                  'width' =>140,
                  'filtertype' => 'yes',
                  'filterdefault' => true,
                  'in_list' => true,
                  'default_in_list' => true,
                  'is_title' => true,
                ),
                'logi_no' =>
                array (
                  'type' => 'varchar(32)',
                  'required' => true,
                  'label' => '快递单号',
                  'editable' => false,
                  'width' =>140,
                  'filtertype' => 'yes',
                  'filterdefault' => true,
                  'in_list' => true,
                  'default_in_list' => true,
                  'is_title' => true,
                ),
                'bn' =>
                array (
                  'type' => 'varchar(30)',
                  'label' => '物料',
                  'editable' => false,
                  'is_title' => true,
                ),
                'unit' =>
                array (
                  'type' => 'varchar(200)',
                  'required' => true,
                  'label' => '单位',
                  'is_title' => true,
                  'default_in_list' => true,
                  'width' => 70,
                  'searchtype' => 'has',
                  'editable' => false,
                  'filtertype' => 'normal',
                  'filterdefault' => true,
                  'in_list' => true,
                ),
                'number' =>
                array (
                  'type' => 'number',
                  'label' => '数量',
                  'required' => true,
                  'default' => 0,
                  'editable' => false,
                ),
                 'pause' =>
                array (
                  'type' => 'varchar(30)',
                  'label' => '是否暂停',
                  'editable' => false,
                  'is_title' => true,
                  'width' => '80',
                ),
                 'ischeck' =>
                array (
                  'type' => 'varchar(30)',
                  'label' => '是否校验',
                  'editable' => false,
                  'is_title' => true,
                  'width' => '80',
                ),
                 'isprint' =>
                array (
                  'type' => 'varchar(30)',
                  'label' => '是否打印',
                  'editable' => false,
                  'is_title' => true,
                  'width' => '80',
                ),
                 'is_form' =>
                array (
                  'type' => 'varchar(30)',
                  'label' => '是否备货',
                  'editable' => false,
                  'is_title' => true,
                  'width' => '80',
                ),
               
               'ship_status' =>
                array (
                  'type' =>
                  array (
                    0 => '未发货',
                    1 => '已发货',
                    2 => '部分发货',
                    3 => '部分退货',
                    4 => '已退货',
                  ),
                  'default'=>'0',
                  'comment' => '发货状态',
                  'editable' => false,
                  'label' => '发货状态',
                  'in_list' => true,
                  'default_in_list' => true,
                  'width' => '60',
                ),
                'pay_status' =>
                array (
                    'type' =>
                    array (
                      0 => '未支付',
                      1 => '已支付',
                      2 => '处理中',
                      3 => '部分付款',
                      4 => '部分退款',
                      5 => '全额退款',
                      6 => '退款申请中',
                      7 => '退款中',
                      8 => '支付中',
                    ),
                  'default'=>'0',
                  'comment' => '付款状态',
                  'editable' => false,
                  'label' => '付款状态',
                  'in_list' => true,
                  'default_in_list' => true,
                  'width' => '60',
                ),


            ),

            'idColumn' => 'name',
            'in_list' => array (
                0 => 'order_bn',
                1 => 'name',
                2 => 'AXCustomer',
                3 => 'customername',
                4 => 'createtime',
                5 => 'delivery_bn',
                6 => 'logi_no',
                7 => 'bn',
                8 => 'unit',
                9 => 'number',
                10 => 'pause',
                11 => 'is_form',
                12 => 'isprint',
                13 => 'ischeck',
                14 => 'ship_status',
                15 => 'pay_status',

            ),
            'default_in_list' => array (
                0 => 'order_bn',
                1 => 'name',
                2 => 'AXCustomer',
                3 => 'customername',
                4 => 'createtime',
                5 => 'delivery_bn',
                6 => 'logi_no',
                7 => 'bn',
                8 => 'unit',
                9 => 'number',
                10 => 'pause',
                11 => 'is_form',
                12 => 'isprint',
                13 => 'ischeck',
                14 => 'ship_status',
                15 => 'pay_status',
            ),

        );
        return $schema;
    }

    /**
     * 获得日志类型(non-PHPdoc)
     * @see dbeav_model::getLogType()
     */
    public function getLogType($logParams) {
        $type = $logParams['type'];
        $logType = 'none';
        if ($type == 'export') {
            $logType = $this->exportLogType($logParams);
        }
        elseif ($type == 'import') {
            $logType = $this->importLogType($logParams);
        }
        return $logType;
    }
    /**
     * 导出日志类型
     * @param Array $logParams 日志参数
     */
    public function exportLogType($logParams) {
        $params = $logParams['params'];
        $type = 'report';
        if ($logParams['app'] == 'omeanalysts' && $logParams['ctl'] == 'ome_delivery') {
            $type .= '_purchaseReport_expressAnalysis';
        }
        $type .= '_export';
        return $type;
    }
    /**
     * 导入操作日志类型
     * @param Array $logParams 日志参数
     */
    public function importLogType($logParams) {
        $params = $logParams['params'];
        $type = 'report';
        if ($logParams['app'] == 'omeanalysts' && $logParams['ctl'] == 'ome_delivery') {
            $type .= '_purchaseReport_expressAnalysis';
        }
        $type .= '_import';
        return $type;
    }

    function io_title( $filter, $ioType='csv' )
    {
//订单号、店铺、客户编码、客户名称、订单时间、发货单号、物流单号、物料、单位、数量、是否暂停、是否校验、是否打印、是否备货
        switch( $filter ){
            case 'delivery':
                $this->oSchema['csv'][$filter] = array(
                    '*:订单号' => 'order_bn',
                    '*:店铺' => 'name',
                    '*:客户编码' => 'AXCustomer',
                    '*:客户名称' => 'customername',
                    '*:订单时间' => 'createtime',
                    '*:发货单号' => 'delivery_bn',
                    '*:物流单号' => 'logi_no',
                    '*:物料' => 'bn',
                    '*:单位' => 'unit',
                    '*:数量' => 'number',
                    '*:是否暂停' => 'pause',
                    '*:是否校验' => 'ischeck',
                    '*:是否打印' => 'isprint',
                    '*:是否备货' => 'is_form',
                    '*:付款状态' => 'pay_status',
                    '*:发货状态' => 'ship_status',
                );
                break;
        }
        $this->ioTitle[$ioType][$filter] = array_keys( $this->oSchema[$ioType][$filter] );
        return $this->ioTitle[$ioType][$filter];
    }

    //根据查询条件获取导出数据
    public function getExportDataByCustom($fields, $filter, $has_detail, $curr_sheet, $start, $end){
          //根据选择的字段定义导出的第一行标题
          if($curr_sheet == 1){

              $title = array();
              foreach($this->io_title('delivery') as $k => $v ){
                  $title[] = mb_convert_encoding($v, 'GBK', 'UTF-8');
              }

              $data['content']['main'][0] = '"'.implode('","',$title).'"';
          }

          $where = $this->_filter($filter);

       $sql = "SELECT DISTINCT os.order_bn ,f.`name`,f.AXCustomer,f.customername,os.createtime,e.delivery_bn,db.logi_no,ositems.bn,ext.unit,items.number,
                    case when os.pause=true then '是' ELSE '否' end pause,
                    case when e.is_form=true then '是' ELSE '否' end is_form,
                    case WHEN e.process_status=0 then '是' else '否'  end isprint,
                    case when e.process_status=3 then  '是' else '否'  end ischeck,
                    os.ship_status,os.pay_status
                    from sdb_ome_orders os 
                    left join sdb_ome_order_items ositems on ositems.order_id =os.order_id
                    left JOIN sdb_ome_delivery_order odo on (odo.order_id = os.order_id)
                    left JOIN sdb_ome_delivery od on ( od.delivery_id = odo.delivery_id )
                    left join sdb_wms_delivery e on (e.outer_delivery_bn =od.delivery_bn)
                    left join sdb_wms_delivery_bill db on e.delivery_id = db.delivery_id 
                    INNER JOIN sdb_wms_delivery_items items ON e.delivery_id=items.delivery_id AND items.bn=ositems.bn 
                    left join sdb_ome_shop f on os.shop_id = f.shop_id
                    left JOIN sdb_material_basic_material_ext ext on ositems.product_id=ext.bm_id 
                   where  ".$where." and os.is_fail='false'  and (os.pay_status in('4','5','6','7') or os.pause='true') ";
            $omedelivery = $this->db->selectLimit($sql,$end,$start);

        $pay_status = array (
                      '0' => '未支付',
                      '1' => '已支付',
                      '2' => '处理中',
                      '3' => '部分付款',
                      '4' => '部分退款',
                      '5' => '全额退款',
                      '6' => '退款申请中',
                      '7' => '退款中',
                      '8' => '支付中',
              );
            foreach ($omedelivery as $key => $value) 
            {
              $omedelivery[$key]['pay_status']=$pay_status[$value['pay_status']];
            }
            foreach ($omedelivery as $k => $aFilter) {
                $exptmp_data = array();
                foreach ($aFilter as $col => $val) {
                    $aFilter[$col] = mb_convert_encoding($aFilter[$col], 'GBK', 'UTF-8');
                    $exptmp_data[] = $aFilter[$col];
                }

                $data['content']['main'][] = implode(',', $exptmp_data);
            }
            return $data;
        
    }
}
